# coding:utf-8
from flask import Flask, jsonify, render_template, redirect, url_for, request
from model.check_login import is_existed, exist_user, is_null
from model.check_regist import add_user
from model.config import conn
import requests
import pandas as pd

app = Flask(__name__)


# 和风天气API
KEY = '3c7db000f9824b1b829741f012fc28ca'
mykey = '&key=' + KEY

# 各项天气数据链接
url_api_weather = 'https://devapi.qweather.com/v7/weather/'  # 实况天气
url_api_geo = 'https://geoapi.qweather.com/v2/city/'  # 城市信息
url_api_rain = 'https://devapi.qweather.com/v7/minutely/5m'  # 降水预报
url_api_air = 'https://devapi.qweather.com/v7/air/now'  # 空气质量

# 登录界面
@app.route('/')
def login():
    return redirect(url_for('user_login'))


@app.route('/user_login', methods=['GET', 'POST'])
def user_login():
    '''
    用户登录
    :return:login.html
    '''
    if request.method == 'POST':  # 注册发送的请求为POST请求
        username = request.form['username']
        password = request.form['password']
        if is_null(username, password):
            login_massage = "温馨提示：账号和密码是必填"
            return render_template('login.html', message=login_massage)
        elif is_existed(username, password):
            return redirect(url_for('index', username=username))
        elif exist_user(username):
            login_massage = "温馨提示：密码错误，请输入正确密码"
            return render_template('login.html', message=login_massage)
        else:
            login_massage = "温馨提示：不存在该用户，请先注册"
            return render_template('login.html', message=login_massage)
    return render_template('login.html')


@app.route("/register", methods=["GET", 'POST'])
def register():
    '''
    用户注册
    :return:register.html
    '''
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        if is_null(username, password):
            login_massage = "温馨提示：账号和密码是必填"
            return render_template('register.html', message=login_massage)
        elif exist_user(username):
            login_massage = "温馨提示：用户已存在，请直接登录"
            return render_template('register.html', message=login_massage)
        else:
            add_user(request.form['username'], request.form['password'])
            return redirect(url_for('index', username=username))
    return render_template('register.html')


# 连接到MySQL数据库的函数
def get_db_connection():
    return conn

# 从MySQL中读取数据到DataFrame的函数
def load_data_from_db():
    connection = get_db_connection()
    query = "SELECT city, date, max_temp, min_temp, weather, wind_direction_power, air_quality FROM data"
    df = pd.read_sql(query, connection)
    df['date'] = pd.to_datetime(df['date'])
    connection.close()
    return df

# 加载数据
df = load_data_from_db()


@app.route('/get_weather_data', methods=['GET'])
def get_weather_data():
    '''
    获取温度数据
    :return:
    '''
    try:
        city = request.args.get('city')
        year = int(request.args.get('year'))
        month = int(request.args.get('month'))

        # 数据筛选
        filtered_df = df[(df['city'] == city) & (df['date'].dt.year == year) & (df['date'].dt.month == month)]

        # 准备数据
        dates = filtered_df['date'].dt.strftime('%Y-%m-%d').tolist()
        high_temps = filtered_df['max_temp'].tolist()
        low_temps = filtered_df['min_temp'].tolist()

        return jsonify({
            'title': f'{year}年{month}月{city}温度变化',
            'dates': dates,
            'high_temps': high_temps,
            'low_temps': low_temps
        })

    except Exception as e:
        return jsonify({'error': str(e)}), 400


@app.route('/get_data', methods=['GET'])
def get_weather_counts():
    '''
    获取天气情况数据
    :return:
    '''
    try:
        city = request.args.get('city')
        year = int(request.args.get('year'))
        month = int(request.args.get('month'))

        filtered_df = df[(df['city'] == city) & (df['date'].dt.year == year) & (df['date'].dt.month == month)]

        weather_counts = filtered_df['weather'].value_counts().to_dict()
        weather_types = list(weather_counts.keys())
        weather_counts_list = list(weather_counts.values())

        return jsonify({
            'title': f'{year}年{month}月{city}天气情况',
            'weather_types': weather_types,
            'weather_counts': weather_counts_list
        })

    except Exception as e:
        return jsonify({'error': str(e)}), 400


@app.route('/get_quality_data', methods=['GET'])
def get_quality_data():
    '''
    获取空气质量数据
    :return:
    '''
    try:
        city = request.args.get('city')
        year = request.args.get('year')

        if not city or not year:
            return jsonify({'error': '缺少必要的参数 city 或 year'}), 400

        year = int(year)
        filtered_df = df[(df['city'] == city) & (df['date'].dt.year == year)].copy()

        filtered_df['air_quality'] = filtered_df['air_quality'].fillna('未知')
        quality_counts = filtered_df['air_quality'].apply(
            lambda x: x.split(' ')[1] if ' ' in x else '未知').value_counts().to_dict()
        quality_data = [{'name': k, 'value': v} for k, v in quality_counts.items()]

        return jsonify({
            'title': f'{year}年{city}空气质量情况',
            'quality_data': quality_data
        })

    except Exception as e:
        return jsonify({'error': str(e)}), 400


@app.route('/get_wind_data', methods=['GET'])
def get_wind_data():
    '''
    获取风力风向数据
    :return:
    '''
    try:
        city = request.args.get('city')
        year = int(request.args.get('year'))
        month = int(request.args.get('month'))

        # 过滤数据
        filtered_df = df[(df['city'] == city) & (df['date'].dt.year == year) & (df['date'].dt.month == month)]

        # 统计风力风向情况
        wind_counts = filtered_df['wind_direction_power'].value_counts().to_dict()
        wind_data = [{'name': k, 'value': v} for k, v in wind_counts.items()]

        return jsonify({
            'title': f'{year}年{month}月{city}风力风向情况',
            'wind_data': wind_data
        })

    except Exception as e:
        return jsonify({'error': str(e)}), 400


@app.route('/data')
def data():
    '''
    分页展示数据
    :return:
    '''
    # 随机抽取100条数据
    sampled_df = df.sample(n=100)
    # 将DataFrame转换为列表的列表
    data = sampled_df.values.tolist()
    headers = sampled_df.columns.tolist()

    # 分页处理
    page = request.args.get('page', 1, type=int)
    per_page = 10
    total = len(data)
    start = (page - 1) * per_page
    end = start + per_page
    paginated_data = data[start:end]

    total_pages = (total + per_page - 1) // per_page  # 计算总页数

    return render_template('data.html', headers=headers, data=paginated_data, page=page, total_pages=total_pages)


# HTML页面渲染
@app.route('/index')
def index():
    username = request.args.get('username')
    return render_template('index.html', username=username)


@app.route('/line')
def line():
    return render_template('line.html')


@app.route('/bar')
def bar():
    return render_template('bar.html')


@app.route('/pie')
def pie():
    return render_template('pie.html')


@app.route('/rose_pie')
def rose_pie():
    return render_template('rose_pie.html')


def get(api_type, city_id=None, lat=None, lon=None):
    '''
    获取api数据
    :param api_type:
    :param city_id:
    :param lat:
    :param lon:
    :return:
    '''
    if api_type == 'minutely':
        url = f'{url_api_rain}?location={lon},{lat}{mykey}'
    else:
        url = f'{url_api_weather}{api_type}?location={city_id}{mykey}'
    return requests.get(url).json()


def air(city_id):
    '''
    获取城市空气质量数据
    :param city_id:
    :return:
    '''
    url = f'{url_api_air}?location={city_id}{mykey}'
    return requests.get(url).json()


def get_city(city_kw):
    '''
    获取城市信息
    :param city_kw:
    :return:
    '''
    url_v2 = f'{url_api_geo}lookup?location={city_kw}{mykey}'
    city = requests.get(url_v2).json()['location'][0]

    city_id = city['id']
    district_name = city['name']
    city_name = city['adm2']
    province_name = city['adm1']
    country_name = city['country']
    lat = city['lat']
    lon = city['lon']

    return city_id, district_name, city_name, province_name, country_name, lat, lon


@app.route('/weather', methods=['GET', 'POST'])
def weather():
    if request.method == 'POST':
        city_input = request.form['city']
        city_idname = get_city(city_input)
        city_id = city_idname[0]

        weather_data = {
            'city_idname': city_idname,
            'now': get('now', city_id=city_id)['now'],
            'daily': get('3d', city_id=city_id)['daily'],
            'hourly': get('24h', city_id=city_id)['hourly'],
            'rain': get('minutely', lat=city_idname[5], lon=city_idname[6])['summary'],
            'air': air(city_id)['now']
        }
        return render_template('weather.html', weather_data=weather_data)

    return render_template('weather.html', weather_data=None)


if __name__ == '__main__':
    app.run(debug=True)  # 设置debug=True以方便调试